package com.ningxia.water.mapper;

import com.ningxia.water.entity.ZtTownInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.ApplicantVo;
import com.ningxia.water.vo.out.ReportVo;
import com.ningxia.water.vo.out.ZonePidVo;
import com.ningxia.water.vo.out.ZoneVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 *
 * @author hz
 * @since 2021-05-14
 */
public interface ZtTownInfoMapper extends BaseMapper<ZtTownInfo> {

    /**
     * 获取乡镇信息
     * @return
     */
    @Select("SELECT\n" +
            "\td.district_id as pid,\n" +
            "\tt.town_id as zoneId,\n" +
            "\tcity_name AS city,\n" +
            "\tdistrict_name AS district,\n" +
            "\ttown_name AS town \n" +
            "FROM\n" +
            "\tzt_district_info d,\n" +
            "\tzt_city_info c,\n" +
            "\tzt_town_info t \n" +
            "WHERE\n" +
            "\td.city_id = c.city_id \n" +
            "\tAND t.district_id = d.district_id")
    List<ZoneVo> getTownInfos();


    /**
     * 获取该乡镇下有多少个乡村
     * @param townId
     * @return
     */
    @Select("SELECT count(1) as num FROM zt_village_info v WHERE v.town_id = #{townId}")
    Integer findByTownId(Integer townId);

    /**
     * 获取该乡镇下的所有用水户
     * @param townId
     * @return
     */
    @Select("SELECT\n" +
            "\tai.applicant_id,\n" +
            "\tai.applicant_name,\n" +
            "\tti.town_name as zoneName,\n" +
            "\tai.zone_id,\n" +
            "\tai.zone_type \n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN zt_town_info ti ON ai.zone_id = ti.town_id \n" +
            "WHERE\n" +
            "\tzone_type = 2 and ai.zone_id = #{townId}")
    List<ApplicantVo> getAllApplicants(Integer townId);

    /**
     * 返回乡镇年报数据
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "ti.town_id as id, " +
            "ti.town_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_town_info ti \n" +
            "LEFT JOIN zt_village_info vi on ti.town_id = vi.town_id\n" +
            "LEFT JOIN ts_well_info wi ON vi.village_id = wi.zone_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time BETWEEN concat(#{currentTime}, '-01') and concat(#{currentTime}, '-12')\n" +
            "GROUP BY ti.town_id")
    List<ReportVo> getTownReportByYear(String currentTime);

    /**
     * 获取乡镇月报数据
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "ti.town_id as id, " +
            "ti.town_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_town_info ti \n" +
            "LEFT JOIN zt_village_info vi on ti.town_id = vi.town_id\n" +
            "LEFT JOIN ts_well_info wi ON vi.village_id = wi.zone_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time = #{currentTime}\n" +
            "GROUP BY ti.town_id")
    List<ReportVo> getTownReportByMonth(String currentTime);

    /**
     * 获取乡镇日报数据
     * @param currentTime 当前查询时间
     * @return
     */
    @Select("SELECT " +
            "ti.town_id as id, " +
            "ti.town_name as name, " +
            "sum(dd.day_data)/10000 as volume\n" +
            "FROM zt_town_info ti \n" +
            "LEFT JOIN zt_village_info vi on ti.town_id = vi.town_id\n" +
            "LEFT JOIN ts_well_info wi ON vi.village_id = wi.zone_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_day_data dd ON wi.well_id = dd.well_id and dd.time = #{currentTime}\n" +
            "GROUP BY ti.town_id")
    List<ReportVo> getTownReportByDay(String currentTime);

    /**
     * 获取乡镇及其对应的区县id
     * @return
     */
    @Select("SELECT\n" +
            "\tdi.district_id AS pid,\n" +
            "\tti.town_id AS id,\n" +
            "\tti.town_name AS name \n" +
            "FROM\n" +
            "\tzt_town_info ti\n" +
            "\tLEFT JOIN zt_district_info di ON ti.district_id = di.district_id")
    List<ZonePidVo> getTownRelationship();

}
